package com.xzakota.minesweeper.db;

import com.xzakota.minesweeper.log.XLogger;
import com.xzakota.minesweeper.model.entity.UserBean;
import com.xzakota.minesweeper.msg.MsgCode;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * rank 数据库访问实现
 *
 * @author Xzakota
 */
public class RankDaoImpl {
    /**
     * 通过用户名查找用户是否存在
     *
     * @param username 用户名
     * @return 是否存在
     */
    public static boolean isUserExist(String username, int roomNum) {
        Connection conn = ConnectionManager.getConnection();
        String sql = "SELECT * FROM `rank` WHERE username = ? AND room = ?";
        PreparedStatement pStatement = null;
        ResultSet resultSet = null;
        try {
            pStatement = conn.prepareStatement(sql);
            pStatement.setString(1, username);
            pStatement.setInt(2, roomNum);
            resultSet = pStatement.executeQuery();
            return resultSet.next();
        } catch (SQLException e) {
            XLogger.error(e);
            return false;
        } finally {
            ConnectionManager.release(resultSet, pStatement, conn);
        }
    }

    /**
     * 通过房间号查找房间里的符合条件的前 count 个用户
     *
     * @param roomNum 房间号
     * @param count   查找个数
     * @return 结果用户集
     */
    public static List<UserBean> findUsers(int roomNum, int count) {
        Connection conn = ConnectionManager.getConnection();
        String sql = """
                WITH RankedData AS (
                    SELECT rd.time, u.*, ROW_NUMBER() OVER (PARTITION BY rd.username ORDER BY rd.time) AS rn
                    FROM `rank` rd
                    JOIN  user u ON rd.username = u.username
                    WHERE rd.room = ?
                )
                SELECT * FROM RankedData WHERE rn = 1 ORDER BY time LIMIT ?;
                """;
        PreparedStatement pStatement = null;
        ResultSet resultSet = null;
        List<UserBean> list = new ArrayList<>();
        try {
            pStatement = conn.prepareStatement(sql);
            pStatement.setInt(1, roomNum);
            pStatement.setInt(2, count);
            resultSet = pStatement.executeQuery();
            while (resultSet.next()) {
                UserBean userBean = new UserBean();
                userBean.username = resultSet.getString("username");
                userBean.score = resultSet.getInt("score");
                userBean.qq = resultSet.getString("qq");
                userBean.time = resultSet.getInt("time");
                list.add(userBean);
            }

            return list;
        } catch (SQLException e) {
            XLogger.error(e);
            return new ArrayList<>();
        } finally {
            ConnectionManager.release(resultSet, pStatement, conn);
        }
    }

    /**
     * 更新指定房间内的用户数据记录
     *
     * @param username 用户名
     * @param roomNum  房间号
     * @param time     数据记录
     * @return 结果码
     */
    public static int updateTime(String username, int roomNum, int time) {
        Connection conn = ConnectionManager.getConnection();
        boolean has = isUserExist(username, roomNum);
        System.out.println(has);
        String sql = has ? """
                    UPDATE `rank` SET time = ?
                    WHERE username = ? AND room = ? AND ? < time;
                """ : "INSERT INTO `rank`(username, time, room) VALUES (?, ?, ?);";
        PreparedStatement pStatement = null;
        ResultSet resultSet = null;
        try {
            pStatement = conn.prepareStatement(sql);
            if (has) {
                pStatement.setInt(1, time);
                pStatement.setString(2, username);
                pStatement.setInt(3, roomNum);
                pStatement.setInt(4, time);
                pStatement.executeUpdate();
            } else {
                pStatement.setString(1, username);
                pStatement.setInt(2, time);
                pStatement.setInt(3, roomNum);
                pStatement.execute();
            }

            return MsgCode.BASE_SUCCESS;
        } catch (SQLException e) {
            XLogger.error(e);
            return MsgCode.BASE_FAIL;
        } finally {
            ConnectionManager.release(resultSet, pStatement, conn);
        }
    }
}
